package com.bbcare.exportExcel.plat.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.bbcare.exportExcel.plat.entity.Account;
import com.bbcare.exportExcel.plat.entity.Archives;
import com.bbcare.exportExcel.plat.entity.Baby;
import com.bbcare.exportExcel.plat.entity.BabyModuleGetscore;
import com.bbcare.exportExcel.plat.entity.BabyQuestionRecord;

public class POITOW {

	 //private static Connection connection = InsertDemoData.getConnection();
	public static Connection connection = null;
	public static void main(String[] args) throws Exception {
		String url = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com:3306/bbcare_cloud?createDatabaseIfNotExist=true&amp;amp;useUnicode=true&amp;amp;characterEncoding=utf8mb4&amp;amp;zeroDateTimeBehavior=convertToNull&amp;amp;transformedBitIsBoolean=true";
		String username = "test_zhangbw";
		String password = "bw123456";
		try {
			connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		//获取乐惠数据
		//getLeHui();
		getAllLeHui();
		//查询松江贝利数据
		//queryBeiLiAnswerAndBabyData();
		// query();
	}
	
	private static void  query() throws Exception{
		//调用存储过程
        CallableStatement cs = connection.prepareCall("{call getsumziliao(?,?)}");
        System.out.println(System.currentTimeMillis());
        //cs.setString(1, "2017-07-01");
       // cs.setString(2, "2017-07-31");
       // ResultSet rs = cs.executeQuery(); //执行查询操作，并获取结果集
        boolean exist = cs.execute();
       // int rowCount = rs.getRow();
        ResultSet resultSet = cs.getResultSet(); 
        System.out.println("exist :" + exist);
        resultSet.last();
        int rowCount = resultSet.getRow();
        System.out.println("rowCount :" + rowCount);
        resultSet.beforeFirst();
        ResultSetMetaData rsmd = resultSet.getMetaData() ; 
        int  columnCount = rsmd.getColumnCount(); 
        int count = 1;
        System.out.println("columnCount :"+columnCount);
        Workbook workbook = new SXSSFWorkbook(100);
		Sheet sheet = workbook.createSheet();
		Row row1 = sheet.createRow(0);
		//Sheet sheet2 = workbook.createSheet("sheet2") ;
        while(resultSet.next())
        {
        	Row row2 = sheet.createRow(count);
        	for(int i=1;i<=columnCount;i++) {
        		String lable  = rsmd.getColumnLabel(i);
            	String value = resultSet.getString(lable);
        		if(count==1) {
        			row1.createCell(i).setCellValue(lable);
        		}
        		row2.createCell(i).setCellValue(value);
        	}
        	System.out.println(count);
        	count++;
        }
        OutputStream outputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\母乳喂养.xls"));
		workbook.write(outputStream);
        workbook.close();
        System.out.println("sss :"+System.currentTimeMillis());
	}
	//查询松江贝利宝宝问卷信息和宝宝信息,并插入Excel表
	private static void queryBeiLiAnswerAndBabyData() throws Exception {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		
		Workbook workbook = new SXSSFWorkbook(100);
		//HSSFWorkbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();

//		String sql = "SELECT * FROM t_answer WHERE _doctorId IN('18862163677','18918282158','18918286562','18918286851','18917169741','18918289961','18116318896','18917169707','13901998071','18918287096','18121173161','18121172701','13918225261','18918286361','18017908556','18918282966','13761634681')";
		String sql = "SELECT * FROM t_answer";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		
		ResultSet resultSet = prepareStatement.executeQuery();
		
		int count = 1;
		while (resultSet.next()) {
			String babyId = resultSet.getString("_babyId");
			
			Account account = queryAccountByBabyId(babyId);
			
			if (babyId == null) {
				continue;
			}
			
			String result = resultSet.getString("_result");
			Date createTime = resultSet.getDate("_createTime");
			String doctorId = resultSet.getString("_doctorId");
			if (doctorId.equals("18755113185") || doctorId.equals("15121111301") || doctorId.equals("18019975051")) {
				continue;
			}
		
			Map<String, Object> map = Json2Map.parseJSON2Map(result);
			Double cognitive = (Double) map.get("cognitive");		//认知
			Double understand = (Double) map.get("understand");		//语言理解
			Double bigAction = (Double) map.get("bigAction");		//大动作
			Double fineMotor = (Double) map.get("fineMotor");		//精细动作
			Double express = (Double) map.get("express");			//语言表达
			
			
			Archives archives = queryBabyArchivesByBabyId(babyId);
			
			if (archives == null) {
				continue;
			}
			
			
		/*	System.out.print(babyId + " ");
			System.out.print(archives.getBabyName() + " ");
			System.out.print(archives.getBirthday() + " ");
			System.out.print(archives.getBirthWeight() + " ");
			System.out.print(archives.getBirthday() + " ");
			System.out.print(archives.getMotherName() + " ");
			System.out.print(archives.getMotherBirthday() + " ");
			System.out.print(archives.getMotherIdCard() + " ");
			System.out.print(cognitive + " ");
			System.out.print(understand + " ");
			System.out.print(express + " ");
			System.out.print(fineMotor + " ");
			System.out.print(bigAction + " ");
			System.out.print(createTime + " ");
			System.out.println(archives.getPregnancyWeek() + " ");*/
			
			
			//插数据
			Row row = sheet.createRow(count);
			row.createCell(0).setCellValue(account.getAccountName());
			if (archives.getBabyName() != null) {
				row.createCell(1).setCellValue(archives.getBabyName());
			}
			if (archives.getBirthday() != null) {
				row.createCell(2).setCellValue(archives.getBirthday());
			}
			if (archives.getBirthWeight() != null) {
				row.createCell(3).setCellValue(archives.getBirthWeight());
			}
			if (archives.getBirthday() != null) {
				row.createCell(4).setCellValue(archives.getBirthday());
			}
			if (archives.getMotherName() != null) {
				row.createCell(5).setCellValue(archives.getMotherName());
			}
			if (archives.getMotherBirthday() != null) {
				row.createCell(6).setCellValue(archives.getMotherBirthday());
			}
			if (archives.getMotherIdCard() != null) {
				row.createCell(7).setCellValue(archives.getMotherIdCard());
			}
			if (cognitive != null) {
				row.createCell(8).setCellValue(cognitive);
			}
			if (understand != null) {
				row.createCell(9).setCellValue(understand);
			}
			if (fineMotor != null) {
				row.createCell(10).setCellValue(express);
			}
			if (fineMotor != null) {
				row.createCell(11).setCellValue(fineMotor);
			}
			if (bigAction != null) {
				row.createCell(12).setCellValue(bigAction);
			}
			if (doctorId != null) {
				row.createCell(13).setCellValue(doctorId);
			}
			if (createTime != null) {
				row.createCell(14).setCellValue(dateFormat.format(createTime));
			}
			if (archives.getPregnancyWeek() != null || archives.getPregnancyWeek().equals("")) {
				row.createCell(15).setCellValue(archives.getPregnancyWeek());
			}
			
			String gender;
			if ("N".equals(archives.getGender())) {
				gender = "男";
			}else{
				gender = "女";
			}
			row.createCell(16).setCellValue(gender);
			
			count++;
		}
		
		prepareStatement.close();
		
		OutputStream output = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\ddd.xls"));
		workbook.write(output);  
		output.close();
	}
	
	private static Account queryAccountByBabyId(String babyId) throws Exception {
		//查询所有松江的用户详情
		String sql = "SELECT a.* FROM t_account a,t_baby b ,t_baby_t_account ba\r\n" + 
				"WHERE a.`_accountId` = ba.`account_id`AND b.`_babyId` = ba.`baby_id` AND b._babyId = '"+ babyId +"'";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		
		
		Account account = new Account();
		if(resultSet.next()){
			account.setAccountName(resultSet.getString("_accountName"));
		}else {
			account = null;
		}
		
		resultSet.close();
		statement.close();
		return account;
	}

	//查询松江贝利宝宝信息
	private static Archives queryBabyArchivesByBabyId(String babyId) throws Exception{
		
		//查询所有松江的用户详情
		String sql = "select * from t_archives WHERE _babyId = '"+ babyId +"' ";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		
		Archives archives = new Archives();
		if(resultSet.next()){
			String id = resultSet.getString("_babyId");
			archives.setBabyId(id);
			archives.setBirthWeight(resultSet.getDouble("_birthWeight"));
			archives.setMotherName(resultSet.getString("_motherName"));
			archives.setMotherBirthday(resultSet.getString("_motherBirthday"));
			archives.setMotherIdCard(resultSet.getString("_motherIdCard"));
			archives.setGender(resultSet.getString("_gender"));
			Baby baby = queryBabyByBabyId(archives.getBabyId());
			archives.setBabyName(baby.getBabyName());
			archives.setBirthday(baby.getBirthday());
			archives.setPregnancyWeek(baby.getPregnancyWeek());
		}else {
			archives = null;
		}
		
			
		
		
		resultSet.close();
		statement.close();
		return archives;
	}
	
	
	private static Baby queryBabyByBabyId(String babyId) throws Exception {
		String sql = "select * from t_baby where _babyId = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, babyId);
		
		ResultSet resultSet = preparedStatement.executeQuery();
		
		Baby baby = new Baby();
		if(resultSet.next()){
			baby.setBabyId(resultSet.getString(1));
			baby.setBabyName(resultSet.getString(3));
			baby.setBirthday(resultSet.getString(4));
			baby.setGender(resultSet.getString("_gender"));
			baby.setPregnancyWeek(resultSet.getInt("_pregnancyWeek"));
		}
		
		resultSet.close();
		preparedStatement.close();
		return baby;
	}
	
	
	//从Excel向t_yunying_agency表里插入数据
	private void insertToAgency() throws IOException{
		File file = new File("C:\\Users\\Administrator\\Desktop\\运营系统补充医院.xls");
		InputStream inputStream = new FileInputStream(file);
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		
		HSSFSheet sheet = workbook.getSheet("Sheet1");
		
		for (int i = 0; i < 20; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row == null) {
				break;
			}
			String agencyId = row.getCell(0).toString();
			String agencyName = row.getCell(1).toString();
			/*String agencyLevel = row.getCell(2).toString();
			System.out.println(agencyLevel);*/
			String city = row.getCell(3).toString();
			
			/*String str = row.getCell(4).toString();
			System.out.println(str);
			int sort = Integer.parseInt(str);*/
			
//			int pcSort = Integer.parseInt(row.getCell(5).toString().trim());
//			String joinTime = row.getCell(7).toString();
			String status = row.getCell(8).toString();
//			int followCount = Integer.parseInt(row.getCell(9).toString().trim());
			String introduce = row.getCell(10).toString();
			String address = row.getCell(11).toString();
			String type = row.getCell(12).toString();
			String keshilist = row.getCell(13).toString();
//			int per = Integer.parseInt(row.getCell(14).toString().trim());
			String imgUrl = row.getCell(15).toString();
			
			try {
				String sql = "insert into t_yunying_agency values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement prepareStatement = connection.prepareStatement(sql);
				prepareStatement.setString(1, agencyId);
				prepareStatement.setString(2, agencyName);
				prepareStatement.setString(3, "03");
				prepareStatement.setString(4, city);
				prepareStatement.setLong(5, 1);
				prepareStatement.setLong(6, 1);
				prepareStatement.setString(7, null);
				prepareStatement.setString(8, status);
				prepareStatement.setLong(9, 2);
				prepareStatement.setString(10, introduce);
				prepareStatement.setString(11, address);
				prepareStatement.setString(12, type);
				prepareStatement.setString(13, keshilist);
				prepareStatement.setLong(14, 2);
				prepareStatement.setString(15, imgUrl);
				
				int j = prepareStatement.executeUpdate();
				System.out.println(j);
				
				prepareStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			
		}
			
		}
	}
	
	
	private static void getAllLeHui() throws SQLException, IOException {
		String sql = "SELECT t._accountName,b._babyName,b._gender,b._birthday,FLOOR(b.`_pregnancyWeek`/7) AS 孕周,b.`_pregnancyWeek`,a.`_birthLength`,a.`_birthWeight`,a.`_createTime`,a.`_fatherBirthday`,a.`_fatherEducation`,a.`_fatherProfession`,a.`_motherBirthday`,a.`_motherEducation`,a.`_motherProfession`,a.`_deliveryMethod`,a.`_pregnantCount`,a.`_city`,a.`_county`,a.`_province`,a.`_hospitalName`,a.`_address`,\r\n" + 
				"t4._score,t5._score,t6._score,t7._score,t8._score,t9._score,t10._score,t11._score,t12._score,t13._note,t14._note,t3._createTime,t3._babyAge\r\n" + 
				"\r\n" + 
				"FROM t_archives a \r\n" + 
				"INNER JOIN t_baby b ON a._babyId=b._babyId AND a._code IN(SELECT _code FROM t_wx_content) \r\n" + 
				"INNER JOIN t_baby_t_account bb ON bb.baby_id=b._babyId\r\n" + 
				"INNER JOIN t_account t ON bb.account_id=t._accountId\r\n" + 
				"INNER JOIN \r\n" + 
				"(SELECT qp._babyId,qp._id FROM t_babyquestionnairepackage qp ) AS t2 ON b._babyId=t2._babyId \r\n" + 
				"INNER JOIN\r\n" + 
				"(SELECT qr._babyQuestionnairePackage_id,qr._id,qr._createTime,qr._babyAge FROM t_babyquestionrecord qr ) AS t3 ON t2._id=t3._babyQuestionnairePackage_id  \r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf2015168029e7a0005' AND ISNULL(gs._score)=0)AS t4 ON t3._id=t4._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf201516802c93a0006' AND ISNULL(gs._score)=0)AS t5 ON t3._id=t5._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf20151680314a60007' AND ISNULL(gs._score)=0)AS t6 ON t3._id=t6._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf201516803403e0008' AND ISNULL(gs._score)=0)AS t7 ON t3._id=t7._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf201516803864a0009' AND ISNULL(gs._score)=0)AS t8 ON t3._id=t8._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf20151680235bd0002' AND ISNULL(gs._score)=0)AS t9 ON t3._id=t9._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf201516802502e0003' AND ISNULL(gs._score)=0)AS t10 ON t3._id=t10._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf2015168026e890004' AND ISNULL(gs._score)=0)AS t11 ON t3._id=t11._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._score,gs._babyQuestionRecordId FROM t_babymodulegetscore gs WHERE  gs._questionModuleId='40288abe5167faf2015168026e890006' AND ISNULL(gs._score)=0)AS t12 ON t3._id=t12._babyQuestionRecordId\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._note,gs._babyQuestionRecord_id FROM t_babyquestiondetail gs WHERE  gs._question_name = '您宝宝的体重是多少？（kg）' AND ISNULL(gs._note)=0)AS t13 ON t3._id=t13._babyQuestionRecord_id\r\n" + 
				"LEFT JOIN\r\n" + 
				"(SELECT gs._note,gs._babyQuestionRecord_id FROM t_babyquestiondetail gs WHERE  gs._question_name = '您宝宝的身长是多少？（cm）' AND ISNULL(gs._note)=0)AS t14 ON t3._id=t14._babyQuestionRecord_id\r\n" + 
				";";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		Workbook workbook = new SXSSFWorkbook(100);
		//HSSFWorkbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		Sheet sheet2 = workbook.createSheet("sheet2") ;
		
		//HSSFSheet sheet = workbook.createSheet();
		//HSSFSheet sheet2 = workbook.createSheet("sheet2") ;
		
		Row row1 = sheet.createRow(0);
		row1.createCell(0).setCellValue("账号");
		row1.createCell(1).setCellValue("姓名");
		row1.createCell(2).setCellValue("性别");
		row1.createCell(3).setCellValue("宝宝生日");
		row1.createCell(4).setCellValue("分娩孕周（周）");
		row1.createCell(5).setCellValue("分娩孕周（天）");
		row1.createCell(6).setCellValue("出生身长");
		row1.createCell(7).setCellValue("出生体重");
		row1.createCell(8).setCellValue("建档时间");
		row1.createCell(9).setCellValue("父亲生日");
		row1.createCell(10).setCellValue("父亲受教育年限");
		row1.createCell(11).setCellValue("父亲工作");
		row1.createCell(12).setCellValue("母亲生日");
		row1.createCell(13).setCellValue("母亲受教育年限");
		row1.createCell(14).setCellValue("母亲职业");
		row1.createCell(15).setCellValue("分娩方式");
		row1.createCell(16).setCellValue("怀孕次数");
		row1.createCell(17).setCellValue("所在城市");
		row1.createCell(18).setCellValue("地区");
		row1.createCell(19).setCellValue("省");
		row1.createCell(20).setCellValue("就诊医院");
		row1.createCell(21).setCellValue("地址");
		row1.createCell(22).setCellValue("智能-沟通（分数）");
		row1.createCell(23).setCellValue("智能-沟通（分类）");
		row1.createCell(24).setCellValue("智能-粗大运动（分数）");
		row1.createCell(25).setCellValue("智能-粗大运动（分类）");
		row1.createCell(26).setCellValue("智能-精细运动（分数）");
		row1.createCell(27).setCellValue("智能-精细运动（分类）");
		row1.createCell(28).setCellValue("智能-解决问题（分数）");
		row1.createCell(29).setCellValue("智能-解决问题（分类）");
		row1.createCell(30).setCellValue("智能-个人社会（分数）");
		row1.createCell(31).setCellValue("智能-个人社会（分类）");
		row1.createCell(32).setCellValue("养育环境-活动空间（分数）");
		row1.createCell(33).setCellValue("养育环境-活动空间（分类）");
		row1.createCell(34).setCellValue("养育环境-活动多样性（分数）");
		row1.createCell(35).setCellValue("养育环境-活动多样性（分类）");
		row1.createCell(36).setCellValue("养育环境-粗大运动玩具（分数）");
		row1.createCell(37).setCellValue("养育环境-粗大运动玩具（分类）");
		row1.createCell(38).setCellValue("养育环境-精细运动玩具（分数）");
		row1.createCell(39).setCellValue("养育环境-精细运动玩具（分类）");
		row1.createCell(40).setCellValue("体重(分数)");
		row1.createCell(41).setCellValue("体重(分类)");
		row1.createCell(42).setCellValue("身高(分数)");
		row1.createCell(43).setCellValue("身高(分类)");
		row1.createCell(44).setCellValue("测试评估日期");
		
		int count = 1;
		boolean falg = false;
		while (resultSet.next()) {
			
			long sheetIndex = count / 65535+ 1;
			//每隔65535条记录后，创建新的sheet
			if (count % 65535== 0) {
				count = 1;
				falg = true;
				
				Row row2 = sheet2.createRow(0);
				row2.createCell(0).setCellValue("账号");
				row2.createCell(1).setCellValue("姓名");
				row2.createCell(2).setCellValue("性别");
				row2.createCell(3).setCellValue("宝宝生日");
				row2.createCell(4).setCellValue("分娩孕周（周）");
				row2.createCell(5).setCellValue("分娩孕周（天）");
				row2.createCell(6).setCellValue("出生身长");
				row2.createCell(7).setCellValue("出生体重");
				row2.createCell(8).setCellValue("建档时间");
				row2.createCell(9).setCellValue("父亲生日");
				row2.createCell(10).setCellValue("父亲受教育年限");
				row2.createCell(11).setCellValue("父亲工作");
				row2.createCell(12).setCellValue("母亲生日");
				row2.createCell(13).setCellValue("母亲受教育年限");
				row2.createCell(14).setCellValue("母亲职业");
				row2.createCell(15).setCellValue("分娩方式");
				row2.createCell(16).setCellValue("怀孕次数");
				row2.createCell(17).setCellValue("所在城市");
				row2.createCell(18).setCellValue("地区");
				row2.createCell(19).setCellValue("省");
				row2.createCell(20).setCellValue("就诊医院");
				row2.createCell(21).setCellValue("地址");
				row2.createCell(22).setCellValue("智能-沟通（分数）");
				row2.createCell(23).setCellValue("智能-沟通（分类）");
				row2.createCell(24).setCellValue("智能-粗大运动（分数）");
				row2.createCell(25).setCellValue("智能-粗大运动（分类）");
				row2.createCell(26).setCellValue("智能-精细运动（分数）");
				row2.createCell(27).setCellValue("智能-精细运动（分类）");
				row2.createCell(28).setCellValue("智能-解决问题（分数）");
				row2.createCell(29).setCellValue("智能-解决问题（分类）");
				row2.createCell(30).setCellValue("智能-个人社会（分数）");
				row2.createCell(31).setCellValue("智能-个人社会（分类）");
				row2.createCell(32).setCellValue("养育环境-活动空间（分数）");
				row2.createCell(33).setCellValue("养育环境-活动空间（分类）");
				row2.createCell(34).setCellValue("养育环境-活动多样性（分数）");
				row2.createCell(35).setCellValue("养育环境-活动多样性（分类）");
				row2.createCell(36).setCellValue("养育环境-粗大运动玩具（分数）");
				row2.createCell(37).setCellValue("养育环境-粗大运动玩具（分类）");
				row2.createCell(38).setCellValue("养育环境-精细运动玩具（分数）");
				row2.createCell(39).setCellValue("养育环境-精细运动玩具（分类）");
				row2.createCell(40).setCellValue("体重(分数)");
				row2.createCell(41).setCellValue("体重(分类)");
				row2.createCell(42).setCellValue("身高(分数)");
				row2.createCell(43).setCellValue("身高(分类)");
				row2.createCell(44).setCellValue("测试评估日期");
			}
			
		   if (falg) {
			    // 创建工作表
			    
			   Row row = sheet2.createRow(count);
				row.createCell(0).setCellValue(resultSet.getString(1));
				row.createCell(1).setCellValue(resultSet.getString(2));
				String babyAge = resultSet.getString("_babyAge");
				int dayAge = Integer.parseInt(babyAge);
				if (resultSet.getString(3).equals("N")) {
					row.createCell(2).setCellValue("男");
				}else {
					row.createCell(2).setCellValue("女");
				}
				row.createCell(3).setCellValue(resultSet.getString(4));
				row.createCell(4).setCellValue(resultSet.getString(5));
				row.createCell(5).setCellValue(resultSet.getString(6));
				row.createCell(6).setCellValue(resultSet.getString(7));
				row.createCell(7).setCellValue(resultSet.getString(8));
				row.createCell(8).setCellValue(resultSet.getString(9).substring(0, 10));
				row.createCell(9).setCellValue(resultSet.getString(10));
				row.createCell(10).setCellValue(resultSet.getString(11));
				row.createCell(11).setCellValue(resultSet.getString(12));
				row.createCell(12).setCellValue(resultSet.getString(13));
				row.createCell(13).setCellValue(resultSet.getString(14));
				row.createCell(14).setCellValue(resultSet.getString(15));
				row.createCell(15).setCellValue(resultSet.getString(16));
				row.createCell(16).setCellValue(resultSet.getString(17));
				row.createCell(17).setCellValue(resultSet.getString(18));
				row.createCell(18).setCellValue(resultSet.getString(19));
				row.createCell(19).setCellValue(resultSet.getString(20));
				row.createCell(20).setCellValue(resultSet.getString(21));
				row.createCell(21).setCellValue(resultSet.getString(22));
				
				row.createCell(22).setCellValue(resultSet.getString(23));
				String gouTong = EvaluateUtils.getResult(dayAge, resultSet.getDouble(23), "智能发育", "沟通");
				if (resultSet.getString(23) == null) {
					gouTong = null;
				}
				row.createCell(23).setCellValue(gouTong);	//沟通分类
				
				row.createCell(24).setCellValue(resultSet.getString(24));
				String cuDa = EvaluateUtils.getResult(dayAge, resultSet.getDouble(24), "智能发育", "粗大动作");
				if (resultSet.getString(24) == null) {
					cuDa = null;
				}
				row.createCell(25).setCellValue(cuDa);
				
				row.createCell(26).setCellValue(resultSet.getString(25));
				String jingXi = EvaluateUtils.getResult(dayAge, resultSet.getDouble(25), "智能发育", "精细运动");
				if (resultSet.getString(25) == null) {
					jingXi = null;
				}
				row.createCell(27).setCellValue(jingXi);
				
				row.createCell(28).setCellValue(resultSet.getString(26));
				String jieJue = EvaluateUtils.getResult(dayAge, resultSet.getDouble(26), "智能发育", "解决问题");
				if (resultSet.getString(26) == null) {
					jieJue = null;
				}
				row.createCell(29).setCellValue(jieJue);
				
				row.createCell(30).setCellValue(resultSet.getString(27));
				String sheHui = EvaluateUtils.getResult(dayAge, resultSet.getDouble(27), "智能发育", "个人-社会");
				if (resultSet.getString(27) == null) {
					sheHui = null;
				}
				row.createCell(31).setCellValue(sheHui);
				
				row.createCell(32).setCellValue(resultSet.getString(28));
				String kongJian = EvaluateUtils.getResult(dayAge, resultSet.getDouble(28), "运动环境", "活动空间");
				if (resultSet.getString(28) == null) {
					kongJian = null;
				}
				row.createCell(33).setCellValue(kongJian);
				
				row.createCell(34).setCellValue(resultSet.getString(29));
				String duoYang = EvaluateUtils.getResult(dayAge, resultSet.getDouble(29), "运动环境", "活动多样性");
				if (resultSet.getString(29) == null) {
					duoYang = null;
				}
				row.createCell(35).setCellValue(duoYang);
				
				
				row.createCell(36).setCellValue(resultSet.getString(30));
				String CDWJ = EvaluateUtils.getResult(dayAge, resultSet.getDouble(30), "运动环境", "粗大运动玩具");
				if (resultSet.getString(30) == null) {
					CDWJ = null;
				}
				row.createCell(37).setCellValue(CDWJ);
				
				row.createCell(38).setCellValue(resultSet.getString(31));
				String JXWJ = EvaluateUtils.getResult(dayAge, resultSet.getDouble(31), "运动环境", "精细运动玩具");
				if (resultSet.getString(31) == null) {
					JXWJ = null;
				}
				row.createCell(39).setCellValue(JXWJ);
				
				row.createCell(40).setCellValue(resultSet.getString(32));
				String tiZhong = EvaluateUtils.getWeightResult(dayAge, resultSet.getDouble(32), resultSet.getString(3));
				if (resultSet.getString(32) == null) {
					tiZhong = null;
				}
				row.createCell(41).setCellValue(tiZhong);
				
				row.createCell(42).setCellValue(resultSet.getString(33));
				String shenGao = EvaluateUtils.getHeightResult(dayAge, resultSet.getDouble(33), resultSet.getString(3));
				if (resultSet.getString(33) == null) {
					shenGao = null;
				}
				row.createCell(43).setCellValue(shenGao);
				
				row.createCell(44).setCellValue(resultSet.getString(34).substring(0, 10));
				System.out.println(count);
				count++;
		   }else {
			   Row row = sheet.createRow(count);
				row.createCell(0).setCellValue(resultSet.getString(1));
				row.createCell(1).setCellValue(resultSet.getString(2));
				String babyAge = resultSet.getString("_babyAge");
				int dayAge = Integer.parseInt(babyAge);
				if (resultSet.getString(3).equals("N")) {
					row.createCell(2).setCellValue("男");
				}else {
					row.createCell(2).setCellValue("女");
				}
				row.createCell(3).setCellValue(resultSet.getString(4));
				row.createCell(4).setCellValue(resultSet.getString(5));
				row.createCell(5).setCellValue(resultSet.getString(6));
				row.createCell(6).setCellValue(resultSet.getString(7));
				row.createCell(7).setCellValue(resultSet.getString(8));
				row.createCell(8).setCellValue(resultSet.getString(9).substring(0, 10));
				row.createCell(9).setCellValue(resultSet.getString(10));
				row.createCell(10).setCellValue(resultSet.getString(11));
				row.createCell(11).setCellValue(resultSet.getString(12));
				row.createCell(12).setCellValue(resultSet.getString(13));
				row.createCell(13).setCellValue(resultSet.getString(14));
				row.createCell(14).setCellValue(resultSet.getString(15));
				row.createCell(15).setCellValue(resultSet.getString(16));
				row.createCell(16).setCellValue(resultSet.getString(17));
				row.createCell(17).setCellValue(resultSet.getString(18));
				row.createCell(18).setCellValue(resultSet.getString(19));
				row.createCell(19).setCellValue(resultSet.getString(20));
				row.createCell(20).setCellValue(resultSet.getString(21));
				row.createCell(21).setCellValue(resultSet.getString(22));
				
				row.createCell(22).setCellValue(resultSet.getString(23));
				String gouTong = EvaluateUtils.getResult(dayAge, resultSet.getDouble(23), "智能发育", "沟通");
				if (resultSet.getString(23) == null) {
					gouTong = null;
				}
				row.createCell(23).setCellValue(gouTong);	//沟通分类
				
				row.createCell(24).setCellValue(resultSet.getString(24));
				String cuDa = EvaluateUtils.getResult(dayAge, resultSet.getDouble(24), "智能发育", "粗大动作");
				if (resultSet.getString(24) == null) {
					cuDa = null;
				}
				row.createCell(25).setCellValue(cuDa);
				
				row.createCell(26).setCellValue(resultSet.getString(25));
				String jingXi = EvaluateUtils.getResult(dayAge, resultSet.getDouble(25), "智能发育", "精细运动");
				if (resultSet.getString(25) == null) {
					jingXi = null;
				}
				row.createCell(27).setCellValue(jingXi);
				
				row.createCell(28).setCellValue(resultSet.getString(26));
				String jieJue = EvaluateUtils.getResult(dayAge, resultSet.getDouble(26), "智能发育", "解决问题");
				if (resultSet.getString(26) == null) {
					jieJue = null;
				}
				row.createCell(29).setCellValue(jieJue);
				
				row.createCell(30).setCellValue(resultSet.getString(27));
				String sheHui = EvaluateUtils.getResult(dayAge, resultSet.getDouble(27), "智能发育", "个人-社会");
				if (resultSet.getString(27) == null) {
					sheHui = null;
				}
				row.createCell(31).setCellValue(sheHui);
				
				row.createCell(32).setCellValue(resultSet.getString(28));
				String kongJian = EvaluateUtils.getResult(dayAge, resultSet.getDouble(28), "运动环境", "活动空间");
				if (resultSet.getString(28) == null) {
					kongJian = null;
				}
				row.createCell(33).setCellValue(kongJian);
				
				row.createCell(34).setCellValue(resultSet.getString(29));
				String duoYang = EvaluateUtils.getResult(dayAge, resultSet.getDouble(29), "运动环境", "活动多样性");
				if (resultSet.getString(29) == null) {
					duoYang = null;
				}
				row.createCell(35).setCellValue(duoYang);
				
				
				row.createCell(36).setCellValue(resultSet.getString(30));
				String CDWJ = EvaluateUtils.getResult(dayAge, resultSet.getDouble(30), "运动环境", "粗大运动玩具");
				if (resultSet.getString(30) == null) {
					CDWJ = null;
				}
				row.createCell(37).setCellValue(CDWJ);
				
				row.createCell(38).setCellValue(resultSet.getString(31));
				String JXWJ = EvaluateUtils.getResult(dayAge, resultSet.getDouble(31), "运动环境", "精细运动玩具");
				if (resultSet.getString(31) == null) {
					JXWJ = null;
				}
				row.createCell(39).setCellValue(JXWJ);
				
				row.createCell(40).setCellValue(resultSet.getString(32));
				String tiZhong = EvaluateUtils.getWeightResult(dayAge, resultSet.getDouble(32), resultSet.getString(3));
				if (resultSet.getString(32) == null) {
					tiZhong = null;
				}
				row.createCell(41).setCellValue(tiZhong);
				
				row.createCell(42).setCellValue(resultSet.getString(33));
				String shenGao = EvaluateUtils.getHeightResult(dayAge, resultSet.getDouble(33), resultSet.getString(3));
				if (resultSet.getString(33) == null) {
					shenGao = null;
				}
				row.createCell(43).setCellValue(shenGao);
				
				row.createCell(44).setCellValue(resultSet.getString(34).substring(0, 10));
				System.out.println(count);
				count++;
		   }
		   
		}
		
		OutputStream outputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\乐惠.xls"));
		
		workbook.write(outputStream);
		workbook.close();
	}
	
	private static List<Archives> getAllArchives() throws SQLException {
		String sql = "SELECT * FROM t_archives";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet resultSet = prepareStatement.executeQuery();
		List<Archives> list = new ArrayList<>();
		
		while(resultSet.next()){
			
			String babyId = resultSet.getString("_babyId");
			if (babyId == null || babyId.equals("")) {
				continue;
			}
			
			Archives archives = new Archives();
			archives.setBabyId(babyId);
			archives.setBabyIdCard(resultSet.getString("_babyIdCard"));
			archives.setBirthWeight(resultSet.getDouble("_birthWeight"));
			archives.setBirthLength(resultSet.getDouble("_birthLength"));
			archives.setCreateTime(resultSet.getString("_createTime"));
			archives.setDeliveryMethod(resultSet.getString("_deliveryMethod"));
			archives.setMotherName(resultSet.getString("_motherName"));
			archives.setMotherProfession(resultSet.getString("_motherProfession"));
			archives.setMotherBirthday(resultSet.getString("_motherBirthday"));
			archives.setMotherWeight(resultSet.getDouble("_motherWeight"));
			archives.setMotherHeight(resultSet.getDouble("_motherHeight"));
			archives.setPregnantCount(resultSet.getInt("_pregnantCount"));
			archives.setDeliverCount(resultSet.getInt("_deliverCount"));
			archives.setMotherEducation(resultSet.getString("_motherEducation"));
			archives.setMotherEducationAges(resultSet.getInt("_motherEducationAges"));
			archives.setMotherIdCard(resultSet.getString("_motherIdCard"));
			archives.setMotherPhone(resultSet.getString("_motherPhone"));
			archives.setFatherProfession(resultSet.getString("_fatherProfession"));
			archives.setFatherEducationAges(resultSet.getInt("_fatherEducationAges"));
			archives.setFatherPhone(resultSet.getString("_fatherPhone"));
			archives.setFatherBirthday(resultSet.getString("_fatherBirthday"));
			archives.setFatherEducation(resultSet.getString("_fatherEducation"));
			archives.setFixedPhone(resultSet.getString("_fixedPhone"));
			archives.setProvince(resultSet.getString("_province"));
			archives.setCity(resultSet.getString("_city"));
			archives.setCounty(resultSet.getString("_county"));
			archives.setCreateTime(resultSet.getString("_createTime"));
			archives.setCode(resultSet.getString("_code"));
			archives.setInpatientNo(resultSet.getString("_inpatientNo"));
			archives.setOutPatientNo(resultSet.getString("_outPatientNo"));
			archives.setSocialCardNo(resultSet.getString("_socialCardNo"));
			archives.setSelfCardNo(resultSet.getString("_selfCardNo"));
			archives.setSmoking(resultSet.getString("_smoking"));
			archives.setSecondhandSmoke(resultSet.getString("_secondhandSmoke"));
			archives.setHospitalName(resultSet.getString("_hospitalName"));
			archives.setAddress(resultSet.getString("_address"));
			archives.setDistrict(resultSet.getString("_district"));
			archives.setPlace(resultSet.getString("_place"));
			archives.setDueDate(resultSet.getString("_dueDate"));
			archives.setEmail(resultSet.getString("_email"));
			archives.setSparephone(resultSet.getString("_sparephone"));
			archives.setBfSituation(resultSet.getString("_bfSituation"));
			archives.setHasBrother(resultSet.getString("_hasBrother"));
			archives.setHasSister(resultSet.getString("_hasSister"));
			archives.setBrotherBirthday1(resultSet.getString("_brotherBirthday1"));
			archives.setBrotherBirthday2(resultSet.getString("_brotherBirthday2"));
			archives.setSisterBirthday1(resultSet.getString("_sisterBirthday1"));
			archives.setSisterBirthday2(resultSet.getString("_sisterBirthday2"));
			archives.setAccountName(resultSet.getString("_accountName"));
			archives.setBabyName(resultSet.getString("_babyName"));
			archives.setBirthday(resultSet.getString("_birthday"));
			archives.setGender(resultSet.getString("_gender"));
			archives.setPregnancyWeek(resultSet.getInt("_pregnancyWeek"));
			
			list.add(archives);
		}
	
		return list;
	}
	
	private static void getLeHui() throws Exception {
		List<Archives> archives = getAllArchives();
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		int count = 0;
		
		HSSFRow row1 = sheet.createRow(0);
		row1.createCell(0).setCellValue("账号");
		row1.createCell(1).setCellValue("姓名");
		row1.createCell(2).setCellValue("性别");
		row1.createCell(3).setCellValue("宝宝身份证号");
		row1.createCell(4).setCellValue("用户编号");
		row1.createCell(5).setCellValue("出生身长");
		row1.createCell(6).setCellValue("出生体重");
		row1.createCell(7).setCellValue("宝宝生日");
		row1.createCell(8).setCellValue("分娩方式");
		row1.createCell(9).setCellValue("妈妈名字");
		row1.createCell(10).setCellValue("母亲生日");
		row1.createCell(11).setCellValue("母亲职业");
		row1.createCell(12).setCellValue("母亲怀孕前体重");
		row1.createCell(13).setCellValue("母亲身高");
		row1.createCell(14).setCellValue("怀孕次数");
		row1.createCell(15).setCellValue("分娩次数");
		row1.createCell(16).setCellValue("母亲学历");
		row1.createCell(17).setCellValue("母亲受教育年限");
		row1.createCell(18).setCellValue("母亲身份证号");
		row1.createCell(19).setCellValue("妈妈电话");
		row1.createCell(20).setCellValue("父亲生日");
		row1.createCell(21).setCellValue("父亲职业");
		row1.createCell(22).setCellValue("父亲学历");
		row1.createCell(23).setCellValue("父亲受教育年限");
		row1.createCell(24).setCellValue("父亲电话");
		row1.createCell(25).setCellValue("固定电话");
		row1.createCell(26).setCellValue("省");
		row1.createCell(27).setCellValue("市");
		row1.createCell(28).setCellValue("区");
		row1.createCell(29).setCellValue("医院引导页医院编号");
		row1.createCell(30).setCellValue("住院号");
		row1.createCell(31).setCellValue("门诊号");
		row1.createCell(32).setCellValue("社保卡号");
		row1.createCell(33).setCellValue("自管卡号");
		row1.createCell(34).setCellValue("怀孕期间是否抽烟");
		row1.createCell(35).setCellValue("怀孕期间是否接触二手烟");
		row1.createCell(36).setCellValue("分娩医院");
		row1.createCell(37).setCellValue("家庭住址");
		row1.createCell(38).setCellValue("随访地点区");
		row1.createCell(39).setCellValue("随访地点XX服务中心");
		row1.createCell(40).setCellValue("预产期");
		row1.createCell(41).setCellValue("邮箱");
		row1.createCell(42).setCellValue("备用电话");
		row1.createCell(43).setCellValue("母乳喂养情况");
		row1.createCell(44).setCellValue("哥哥个数");
		row1.createCell(45).setCellValue("姐姐个数");
		row1.createCell(46).setCellValue("哥哥生日1");
		row1.createCell(47).setCellValue("哥哥生日2");
		row1.createCell(48).setCellValue("姐姐生日1");
		row1.createCell(49).setCellValue("姐姐生日2");
		row1.createCell(50).setCellValue("性别");
		row1.createCell(51).setCellValue("孕周(天)");
		row1.createCell(52).setCellValue("孕周(周)");
		row1.createCell(53).setCellValue("建档时间");
		
		
		
		row1.createCell(54).setCellValue("智能-沟通（分数）");
		row1.createCell(55).setCellValue("智能-沟通（分类）");
		row1.createCell(56).setCellValue("智能-粗大运动（分数）");
		row1.createCell(57).setCellValue("智能-粗大运动（分类）");
		row1.createCell(58).setCellValue("智能-精细运动（分数）");
		row1.createCell(59).setCellValue("智能-精细运动（分类）");
		row1.createCell(60).setCellValue("智能-解决问题（分数）");
		row1.createCell(61).setCellValue("智能-解决问题（分类）");
		row1.createCell(62).setCellValue("智能-个人社会（分数）");
		row1.createCell(63).setCellValue("智能-个人社会（分类）");
		row1.createCell(64).setCellValue("养育环境-活动空间（分数）");
		row1.createCell(65).setCellValue("养育环境-活动空间（分类）");
		row1.createCell(66).setCellValue("养育环境-活动多样性（分数）");
		row1.createCell(67).setCellValue("养育环境-活动多样性（分类）");
		row1.createCell(68).setCellValue("养育环境-粗大运动玩具（分数）");
		row1.createCell(69).setCellValue("养育环境-粗大运动玩具（分类）");
		row1.createCell(70).setCellValue("养育环境-精细运动玩具（分数）");
		row1.createCell(71).setCellValue("养育环境-精细运动玩具（分类）");
		row1.createCell(72).setCellValue("体重(分数)");
		row1.createCell(73).setCellValue("体重(分类)");
		row1.createCell(74).setCellValue("身高(分数)");
		row1.createCell(75).setCellValue("身高(分类)");
		row1.createCell(76).setCellValue("测试评估日期");
		
		for (Archives archives2 : archives) {
			Baby baby = queryBabyByBabyId(archives2.getBabyId());
			if (baby == null || baby.getGender() == null || archives2.getCreateTime() == null) {
				continue;
			}
			
			archives2.setBabyName(baby.getBabyName());
			archives2.setGender(baby.getGender());
			archives2.setBirthday(baby.getBirthday());
			archives2.setPregnancyWeek(baby.getPregnancyWeek());
			
			
			
			List<String> times = getTimeByBabyId(archives2.getBabyId());
			for (String time : times) {
				
				HSSFRow row = sheet.createRow(++count);
				System.out.println(count + "  " + baby.getBabyName() );
				row.createCell(0).setCellValue(archives2.getAccountName());
				row.createCell(1).setCellValue(archives2.getBabyName());
				if (archives2.getGender().equals("N")) {
					row.createCell(2).setCellValue("男");
				}else {
					row.createCell(2).setCellValue("女");
				}
				
				row.createCell(3).setCellValue(archives2.getBabyIdCard());
				row.createCell(4).setCellValue(archives2.getUserId());
				row.createCell(5).setCellValue(archives2.getBirthLength());
				row.createCell(6).setCellValue(archives2.getBirthWeight());
				row.createCell(7).setCellValue(archives2.getBirthday());
				row.createCell(8).setCellValue(archives2.getDeliveryMethod());
				row.createCell(9).setCellValue(archives2.getMotherName());
				row.createCell(10).setCellValue(archives2.getMotherBirthday());
				row.createCell(11).setCellValue(archives2.getMotherProfession());
				row.createCell(12).setCellValue(archives2.getMotherWeight());
				row.createCell(13).setCellValue(archives2.getMotherHeight());
				row.createCell(14).setCellValue(archives2.getPregnantCount());
				row.createCell(15).setCellValue(archives2.getDeliverCount());
				row.createCell(16).setCellValue(archives2.getMotherEducation());
				row.createCell(17).setCellValue(archives2.getMotherEducationAges());
				row.createCell(18).setCellValue(archives2.getMotherIdCard());
				row.createCell(19).setCellValue(archives2.getMotherPhone());
				row.createCell(20).setCellValue(archives2.getFatherBirthday());
				row.createCell(21).setCellValue(archives2.getFatherProfession());
				row.createCell(22).setCellValue(archives2.getFatherEducation());
				row.createCell(23).setCellValue(archives2.getFatherEducationAges());
				row.createCell(24).setCellValue(archives2.getFatherPhone());
				row.createCell(25).setCellValue(archives2.getFixedPhone());
				row.createCell(26).setCellValue(archives2.getProvince());
				row.createCell(27).setCellValue(archives2.getCity());
				row.createCell(28).setCellValue(archives2.getCounty());
				row.createCell(29).setCellValue(archives2.getCode());
				row.createCell(30).setCellValue(archives2.getInpatientNo());
				row.createCell(31).setCellValue(archives2.getOutPatientNo());
				row.createCell(32).setCellValue(archives2.getSocialCardNo());
				row.createCell(33).setCellValue(archives2.getSelfCardNo());
				row.createCell(34).setCellValue(archives2.getSmoking());
				row.createCell(35).setCellValue(archives2.getSecondhandSmoke());
				row.createCell(36).setCellValue(archives2.getHospitalName());
				row.createCell(37).setCellValue(archives2.getAddress());
				row.createCell(38).setCellValue(archives2.getDistrict());
				row.createCell(39).setCellValue(archives2.getPlace());
				row.createCell(40).setCellValue(archives2.getDueDate());
				row.createCell(41).setCellValue(archives2.getEmail());
				row.createCell(42).setCellValue(archives2.getSparephone());
				row.createCell(43).setCellValue(archives2.getBfSituation());
				row.createCell(44).setCellValue(archives2.getHasBrother());
				row.createCell(45).setCellValue(archives2.getHasSister());
				row.createCell(46).setCellValue(archives2.getBrotherBirthday1());
				row.createCell(47).setCellValue(archives2.getBrotherBirthday2());
				row.createCell(48).setCellValue(archives2.getSisterBirthday1());
				row.createCell(49).setCellValue(archives2.getSisterBirthday2());
				row.createCell(50).setCellValue(archives2.getGender());
				row.createCell(51).setCellValue(archives2.getPregnancyWeek());
				row.createCell(52).setCellValue(archives2.getPregnancyWeek()/7);
				row.createCell(53).setCellValue(archives2.getCreateTime().substring(0, 10));
				
				List<BabyQuestionRecord> babyquestionRecords = getBabyquestionRecordByBabyId(archives2.getBabyId(), time);
				for (BabyQuestionRecord babyQuestionRecord : babyquestionRecords) {
					Integer babyAge = babyQuestionRecord.getBabyAge();
					//生长发育
					if ("生长发育评价".equals(babyQuestionRecord.getBabyQuestionnairePackageName())) {
						String weight = getWeight(babyQuestionRecord.getId());
						if (weight != null) {
							row.createCell(72).setCellValue(weight);
							String tiZhong = EvaluateUtils.getWeightResult(babyAge, Double.parseDouble(weight), archives2.getGender());
							if (weight == null) {
								tiZhong = null;
							}
							row.createCell(73).setCellValue(tiZhong);
							
						}
						
						
						
						String height = getHeight(babyQuestionRecord.getId());
						if (height != null) {
							row.createCell(74).setCellValue(height);
							String shenGao = EvaluateUtils.getHeightResult(babyAge, Double.parseDouble(height), archives2.getGender());
							if (height == null) {
								shenGao = null;
							}
							row.createCell(75).setCellValue(shenGao);
							
						}
					}else{
					
						//智力发育和养育环境
						List<BabyModuleGetscore> BabyModuleGetscores = getBabyModuleGetscoreByBabyQuestionRecordId(babyQuestionRecord.getId());
						for (BabyModuleGetscore babyModuleGetscore : BabyModuleGetscores) {
							switch (babyModuleGetscore.getQuestionModuleId()) {
							case "40288abe5167faf2015168029e7a0005": //沟通
								row.createCell(54).setCellValue(babyModuleGetscore.getScore());
								String gouTong = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "智能发育", "沟通");
								if (babyModuleGetscore.getScore() == null) {
									gouTong = null;
								}
								row.createCell(55).setCellValue(gouTong);	//沟通分类
								
								break;
							case "40288abe5167faf201516802c93a0006": //粗大动作
								row.createCell(56).setCellValue(babyModuleGetscore.getScore());
								String cuDa = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "智能发育", "粗大动作");
								if (babyModuleGetscore.getScore() == null) {
									cuDa = null;
								}
								row.createCell(57).setCellValue(cuDa);
								break;
							case "40288abe5167faf20151680314a60007": //精细动作
								row.createCell(58).setCellValue(babyModuleGetscore.getScore());
								String jingXi = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "智能发育", "精细运动");
								if (babyModuleGetscore.getScore() == null) {
									jingXi = null;
								}
								row.createCell(59).setCellValue(jingXi);
								
								
								break;
							case "40288abe5167faf201516803403e0008": //解决问题
								row.createCell(60).setCellValue(babyModuleGetscore.getScore());
								String jieJue = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "智能发育", "解决问题");
								if (babyModuleGetscore.getScore() == null) {
									jieJue = null;
								}
								row.createCell(61).setCellValue(jieJue);
								
								break;
							case "40288abe5167faf201516803864a0009": //个人-社会
								row.createCell(62).setCellValue(babyModuleGetscore.getScore());
								String sheHui = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "智能发育", "个人-社会");
								if (babyModuleGetscore.getScore() == null) {
									sheHui = null;
								}
								row.createCell(63).setCellValue(sheHui);
								
								break;
							case "40288abe5167faf20151680235bd0002":
								row.createCell(64).setCellValue(babyModuleGetscore.getScore());
								String kongJian = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "运动环境", "活动空间");
								if (babyModuleGetscore.getScore() == null) {
									kongJian = null;
								}
								row.createCell(65).setCellValue(kongJian);
								break;
							case "40288abe5167faf201516802502e0003":
								
								row.createCell(66).setCellValue(babyModuleGetscore.getScore());
								String duoYang = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "运动环境", "活动多样性");
								if (babyModuleGetscore.getScore() == null) {
									duoYang = null;
								}
								row.createCell(67).setCellValue(duoYang);
								
								
								break;
							case "40288abe5167faf2015168026e890004":
								
								row.createCell(68).setCellValue(babyModuleGetscore.getScore());
								String CDWJ = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "运动环境", "粗大运动玩具");
								if (babyModuleGetscore.getScore() == null) {
									CDWJ = null;
								}
								row.createCell(69).setCellValue(CDWJ);
								
								
								break;
							case "40288abe5167faf2015168026e890006":
								row.createCell(70).setCellValue(babyModuleGetscore.getScore());
								String JXWJ = EvaluateUtils.getResult(babyAge, babyModuleGetscore.getScore(), "运动环境", "精细运动玩具");
								if (babyModuleGetscore.getScore() == null) {
									JXWJ = null;
								}
								row.createCell(71).setCellValue(JXWJ);
								
								break;
							default:
								break;
							}
						}
					
					}
					
					
					
					
				}
				row.createCell(76).setCellValue(time);
			}
			
			
		}
		
		OutputStream outputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\ABC.xls"));
		
		workbook.write(outputStream);
		workbook.close();

		
	}
	
	
	

	private static String getWeight(String id) throws Exception {
		
		return getWeightOrHeight(id,"weight");
	}
	private static String getHeight(String id) throws Exception {
		
		return getWeightOrHeight(id,"height");
	}

	private static String getWeightOrHeight(String id,String stat) throws Exception {
		String sql = "SELECT _note FROM t_babyquestiondetail WHERE _BabyQuestionRecord_id = ? AND _question_name = ?";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setString(1, id);
		if (stat.equals("weight")) {
			prepareStatement.setString(2, "您宝宝的体重是多少？（kg）");
		}else{
			prepareStatement.setString(2, "您宝宝的身长是多少？（cm）");
		}
		
		ResultSet resultSet = prepareStatement.executeQuery();
		
		String data = null;
		while(resultSet.next()){
			data = resultSet.getString("_note");
		}
		
		resultSet.close();
		prepareStatement.close();
		return data;
	}

	private static List<BabyQuestionRecord> getBabyquestionRecordByBabyId(String babyId,String createTime) throws Exception {
		String sql = "SELECT _id,_babyAge,_babyQuestionnairePackage_name FROM t_babyquestionrecord WHERE _babyQuestionnairePackage_id \r\n" + 
				"IN (SELECT _id FROM t_babyquestionnairepackage WHERE _babyId = ?) AND _submitTime IS NOT NULL AND _createTime LIKE '"+createTime+"%' " + 
				"GROUP BY DATE_FORMAT(_createTime,'%Y-%m-%d'),_babyQuestionnairePackage_id";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setString(1, babyId);
		ResultSet resultSet = prepareStatement.executeQuery();
		
		List<BabyQuestionRecord> list = new ArrayList<>();
		while (resultSet.next()) {
			
			BabyQuestionRecord babyQuestionRecord = new BabyQuestionRecord();
			babyQuestionRecord.setId(resultSet.getString("_id"));
			babyQuestionRecord.setBabyAge(resultSet.getInt("_babyAge"));
			babyQuestionRecord.setBabyQuestionnairePackageName(resultSet.getString("_babyQuestionnairePackage_name"));
			list.add(babyQuestionRecord);
			
		}
		
		return list;
	}
	
	//根据babyId获取有哪些日期做了测评
	private static List<String> getTimeByBabyId(String babyId) throws Exception {
		String sql = "SELECT DISTINCT DATE_FORMAT(_createTime,'%Y-%m-%d') createTime FROM t_babyquestionrecord WHERE _babyQuestionnairePackage_id \r\n" + 
				"IN (SELECT _id FROM t_babyquestionnairepackage WHERE _babyId = ?) AND _submitTime IS NOT NULL";

		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setString(1, babyId);
		ResultSet resultSet = prepareStatement.executeQuery();
		List<String> list = new ArrayList<>();
		while (resultSet.next()) {
			list.add(resultSet.getString("createTime"));
		}
		
		return list;
	}

	//根据babyId获取有哪些日期做了测评
	private static List<BabyModuleGetscore> getBabyModuleGetscoreByBabyQuestionRecordId(String babyQuestionRecordId) throws Exception {
		String sql = "SELECT * FROM t_babymodulegetscore WHERE  _babyQuestionRecordId = ?";
		
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setString(1, babyQuestionRecordId);
		ResultSet resultSet = prepareStatement.executeQuery();
		List<BabyModuleGetscore> list = new ArrayList<>();
		while (resultSet.next()) {
			BabyModuleGetscore babyModuleGetscore = new BabyModuleGetscore();
			babyModuleGetscore.setQuestionModuleId(resultSet.getString("_questionModuleId"));
			babyModuleGetscore.setScore(resultSet.getDouble("_score"));
			
			list.add(babyModuleGetscore);
		}
		
		return list;
	}
	
}
